/*** TABLES AND FIGURES -- TRACKING MEASURE SAMPLE ***/

clear all
set more 1

do "paths.do"

capture log close
log using "$LOGFILES\tracking_9.log", replace


/************************************************************
Figure 1: District-level variance in math scores accounted
for by schools, classes
F1-capacity
*************************************************************/

use "$WORKING\tracking_7_dis_sch", clear
keep grade year distnum0 subj dtrack*
save "$WORKING\temp/tracking_9-f1_0.dta", replace

use "$WORKING\tracking_7", clear
keep campus grade year distnum0 subj ztrack* schsz
merge m:1 grade year distnum0 subj using "$WORKING\temp/tracking_9-f1_0.dta"
gen dis_merge = _merge
drop _merge

gen trk_sch_fa = ztrackR
gen trk_sch_mr = (ztrackR - ztrackRavg) / (ztrackRavg_max - ztrackRavg)
gen trk_dis_fa = dtrackR
gen trk_dis_mr = (dtrackR - dtrackRavg) / (dtrackRavg_max - dtrackRavg)

save "$WORKING\temp/tracking_9-f1_1.dta", replace

use "$WORKING\temp/tracking_9-f1_1.dta", clear
gen t0 = floor(trk_sch_fa * 20) / 20
collapse (sum) nstud_sch_fa=schsz, by(dis_merge subj t0)
replace nstud_sch_fa = . if (nstud_sch_fa <= 5)
save "$WORKING\temp/tracking_9-f1_2.dta", replace

use "$WORKING\temp/tracking_9-f1_1.dta", clear
gen t0 = floor(trk_sch_mr * 20) / 20
collapse (sum) nstud_sch_mr=schsz, by(dis_merge subj t0)
replace nstud_sch_mr = . if (nstud_sch_mr <= 5)
save "$WORKING\temp/tracking_9-f1_3.dta", replace

use "$WORKING\temp/tracking_9-f1_1.dta", clear
gen t0 = floor(trk_dis_fa * 20) / 20
collapse (sum) nstud_dis_fa=schsz, by(dis_merge subj t0)
replace nstud_dis_fa = . if (nstud_dis_fa <= 5)
save "$WORKING\temp/tracking_9-f1_4.dta", replace

use "$WORKING\temp/tracking_9-f1_1.dta", clear
gen t0 = floor(trk_dis_mr * 20) / 20
collapse (sum) nstud_dis_mr=schsz, by(dis_merge subj t0)
replace nstud_dis_mr = . if (nstud_dis_mr <= 5)
save "$WORKING\temp/tracking_9-f1_5.dta", replace

use "$WORKING\temp/tracking_9-f1_2.dta", clear
merge 1:1 dis_merge subj t0 using "$WORKING\temp/tracking_9-f1_3.dta"
drop _merge
merge 1:1 dis_merge subj t0 using "$WORKING\temp/tracking_9-f1_4.dta"
drop _merge
merge 1:1 dis_merge subj t0 using "$WORKING\temp/tracking_9-f1_5.dta"
drop _merge

save "$WORKING\temp/tracking_9-f1.dta", replace
export delimited using "$OUTPUT\tracking_9-f1.csv", replace

/************************************************************
Figure 2: District-level variance in race, SES accounted
for by schools, classes
*************************************************************/

use "$WORKING\tracking_8_dis_sch.dta", clear
keep grade year distnum0 subj dtrack*
save "$WORKING\temp/tracking_9-f2_0.dta", replace

use "$WORKING\tracking_8.dta", clear
keep campus grade year distnum0 subj track* schsz
merge m:1 grade year distnum0 subj using "$WORKING\temp/tracking_9-f2_0.dta"
gen dis_merge = _merge
drop _merge

foreach vv in poc frpl {
	gen trk_sch_fa_`vv' = trackr_`vv'
	gen trk_sch_mr_`vv' = (trackr_`vv' - trackravg_`vv') / (trackravg_max_`vv' - trackravg_`vv')
	gen trk_dis_fa_`vv' = dtrackr_`vv'
	gen trk_dis_mr_`vv' = (dtrackr_`vv' - dtrackravg_`vv') / (dtrackravg_max_`vv' - dtrackravg_`vv')
}

assert subj == "math"
save "$WORKING\temp/tracking_9-f2_1.dta", replace

local k = 0
foreach vv in poc frpl {
	foreach zz in fa mr {
		foreach ds in dis sch {
			use "$WORKING\temp/tracking_9-f2_1.dta", clear
			gen t0 = floor(trk_`ds'_`zz'_`vv' * 20) / 20
			collapse (sum) nstud_`ds'_`zz'_`vv'=schsz, by(dis_merge t0)
			replace nstud_`ds'_`zz'_`vv' = . if (nstud_`ds'_`zz'_`vv' <= 5)
			save "$WORKING\temp/tracking_9-f2_`ds'_`zz'_`vv'.dta", replace
			
			local k = `k' + 1
			if (`k' > 1) {
				use "$WORKING\temp/tracking_9-f2_2.dta", clear
				merge 1:1 dis_merge t0 using "$WORKING\temp/tracking_9-f2_`ds'_`zz'_`vv'.dta"
				drop _merge
			}
			save "$WORKING\temp/tracking_9-f2_2.dta", replace
		}
	}
}

** Note: significant number of obs with values outside this range. They are ALL
** accounted for by obs with 1 poc or non-poc, or 1 frpl or non-frpl. So the
** diff between random assignment and purposeful assignment is close to 0.
keep if (-0.5 <= t0) & (t0 <= 1.5)

save "$WORKING\temp/tracking_9-f2.dta", replace
export delimited using "$OUTPUT\tracking_9-f2.csv", replace


/************************************************************
Table 2: Decompose Test Score Variance
T1-capacity
*************************************************************/

** Sample to use
use campus distnum0 grade year Sservicex Sclass_id LMscore zLMscore Dblack Dhisp Pdisadv id1 using "$WORKING\tracking_6-math", clear
keep if (grade >= 4) & (grade <= 8)

** Target variables
gen D_poc = Dblack | Dhisp
gen D_frpl = (Pdisadv > 0)
gen D_zsco = zLMscore
foreach vv in zsco poc frpl {
    gen D_`vv'2 = D_`vv' ^ 2
	gen has_`vv' = (D_`vv' != .)
}

** Collapse to class level
collapse (sum) has_* D_*, by(distnum0 campus grade year Sclass_id)
** Rename sums
foreach vv in zsco poc frpl {
	rename D_`vv' sum1_sclass_`vv'
	rename D_`vv'2 sum2_sclass_`vv'
}

** District identifiers
gen district = distnum0

** Group all together
gen split_all = 1
** Group elementary and middle grades
gen split_elem = (grade <= 5)
** Group districts with few and many campuses
egen tmp1 = tag(grade year district campus)
egen camp_in_dist = sum(tmp1), by(grade year district)
egen min_camp = min(camp_in_dist), by(district)
drop tmp*
gen split_mincamp = 99
replace split_mincamp = 1 if (min_camp == 1)
replace split_mincamp = 5 if (min_camp <= 5) & (min_camp > 1)
** Combo group
gen split_both = (split_elem * 1000) + split_mincamp

egen h_gyd = group(grade year district)
egen h_gydc = group(grade year district campus)
egen h_gydcc = group(grade year district campus Sclass_id)

save "$WORKING\temp/tracking_9_0.dta", replace

foreach ss in all elem mincamp both {
	use "$WORKING\temp/tracking_9_0.dta", clear
	
	gen split_value = split_`ss'
	gen n_dist = 1
	egen tmp0 = tag(split_value district campus)
	egen n_camp = sum(tmp0), by(split_value district)
	egen n_studyear = sum(has_poc), by(split_value district)
	egen tmp1 = tag(split_value district)
	keep if tmp1
	
	
	collapse (sum) n_dist n_camp n_studyear, by(split_value)
	gen split_variable = "`ss'"
	
	
	save "$WORKING\temp/tracking_9_2_`ss'.dta", replace
	
	
	
	use "$WORKING\temp/tracking_9_0.dta", clear
	
	egen g_state = group(split_`ss')
	egen g_gy = group(split_`ss' grade year)
	egen g_gyd = group(split_`ss' grade year district)
	egen g_gydc = group(split_`ss' grade year district campus)
	egen g_gydcc = group(split_`ss' grade year district campus Sclass_id)


	foreach vv in zsco poc frpl {
		
		gen n_sclass = has_`vv'
		egen n_state_`vv' = sum(n_sclass), by(g_state)
		egen sum1_state_`vv' = sum(sum1_sclass_`vv'), by(g_state)
		egen sum2_state_`vv' = sum(sum2_sclass_`vv'), by(g_state)
		
		gen lambda_state_`vv' = (sum1_state_`vv' ^ 2) / n_state_`vv'
		gen iota_state_`vv' = sum2_state_`vv'
		
		foreach grp in gy gyd gydc gydcc {
			egen n_`grp'_`vv' = sum(n_sclass), by(g_`grp')
			egen sum1_`grp'_`vv' = sum(sum1_sclass_`vv'), by(g_`grp')
			
			gen kd0 = (sum1_`grp'_`vv' ^ 2) / n_`grp'_`vv'
			egen kd1 = tag(g_`grp')
			egen kappa_`grp'_`vv' = sum(kd0) if kd1, by(g_state)
			gen rho_`grp'_`vv' = (kappa_`grp'_`vv' - lambda_state_`vv') / (iota_state_`vv' - lambda_state_`vv')
			
			drop kd*
		}
		
		drop n_sclass
	}
	
	gen tmp1 = ((rho_gyd_poc != .) & (rho_gyd_frpl != .) & (rho_gyd_zsco != .))
	keep if tmp1
	egen tmp2 = tag(split_`ss')
	keep if tmp2
	drop tmp*
	
	gen split_variable = "`ss'"
	gen split_value = split_`ss'
	
	keep split_variable split_value rho_*
	
	merge 1:1 split_variable split_value using "$WORKING\temp/tracking_9_2_`ss'.dta"
	save "$WORKING\temp/tracking_9_1_`ss'.dta", replace
}

local k = 0
foreach ss in all elem mincamp both {
	if (`k' == 0){
		use "$WORKING\temp/tracking_9_1_`ss'.dta", clear
	}
	else {
		append using "$WORKING\temp/tracking_9_1_`ss'.dta"
	}
	local k = `k' + 1
}

gen sv_id = 1
replace sv_id = 2 if split_variable == "mincamp"
replace sv_id = 3 if (split_variable == "elem") & (split_value == 1)
replace sv_id = 4 if (split_variable == "both") & (split_value >= 1000)
replace sv_id = 5 if (split_variable == "elem") & (split_value == 0)
replace sv_id = 6 if (split_variable == "both") & (split_value < 1000)

sort sv_id split_variable split_value
order sv_id split_variable split_value rho_*
save "$WORKING\temp/tracking_9-td1.dta", replace

drop _merge
export delimited using "$OUTPUT\tracking_9-t1.csv", replace

/************************************************************
Figure 5: Tracking Map
F5-map
*************************************************************/

use "$WORKING\tracking_7.dta", clear

keep subj campus distnum0 grade year schsz zt* CG*LM

gen trk_fa_mnsch = ztrackR
gen trk_mr_mnsch = (ztrackR - ztrackRavg) / (ztrackRavg - ztrackRavg_max)

gen trk_fa_mnsch_wt = trk_fa_mnsch * schsz
gen trk_mr_mnsch_wt = trk_mr_mnsch * schsz
gen prev_score_mn_wt = CGmnzLM * schsz
gen prev_score_sd_wt = CGsdzLM * schsz

collapse (sum) schsz trk_fa_mnsch_wt trk_mr_mnsch_wt prev_score_mn_wt prev_score_sd_wt, by(subj distnum0 grade year)
keep if schsz > 5

foreach vv in trk_fa_mnsch trk_mr_mnsch prev_score_mn prev_score_sd {
	gen `vv' = `vv'_wt / schsz
}

drop *_wt

export delimited using "$WORKING\tracking_9-f5.csv", replace


/************************************************************
Table 3: Correlation in Tracking Across Subjects
T2-subj
*************************************************************/

use "$WORKING/tracking_8.dta", clear
keep campus grade year trackr*
foreach vv in poc frpl{
	gen trk_fa_`vv' = trackr_`vv'
	gen trk_mr_`vv' = (trackr_`vv' - trackravg_`vv') / (trackravg_max_`vv' - trackravg_`vv')
}
keep campus grade year trk_*
save "$WORKING/temp/tracking_9-t2_0.dta", replace

use "$WORKING/tracking_7.dta", clear
keep subj campus grade year schsz
keep if subj == "math"
drop subj
save "$WORKING/temp/tracking_9-t2_1.dta", replace

use "$WORKING/tracking_7.dta", clear
keep subj campus grade year schsz numcls ztrackr ztrackravg ztrackrsd ztrackravg_max
gen trk_fa_ = ztrackr
gen trk_mr_ = (ztrackr - ztrackravg) / (ztrackravg_max - ztrackravg)
keep subj campus grade year trk_*
reshape wide trk_*, string i(campus grade year) j(subj)
save "$WORKING/temp/tracking_9-t2_2.dta", replace

merge 1:1 campus grade year using "$WORKING/temp/tracking_9-t2_0.dta"
keep if _merge == 3
drop _merge
merge 1:1 campus grade year using "$WORKING/temp/tracking_9-t2_1.dta"
keep if _merge == 3
drop _merge


save "$WORKING/temp/tracking_9-t2_3.dta", replace

use "$WORKING/temp/tracking_9-t2_3.dta", clear
gen tmp1 = 1
foreach vv in ela math sci soc poc frpl {
	replace tmp1 = 0 if (trk_fa_`vv' == .) | (trk_mr_`vv' == .)
}
replace tmp1 = 0 if (schsz == .)
tab tmp1
keep if tmp1 == 1
drop tmp*

cor trk_* if inlist(grade, 4, 5, 6, 7, 8) [weight=schsz]
matrix cor_out_48 = r(C)
cor trk_* if inlist(grade, 4, 5) [weight=schsz]
matrix cor_out_45 = r(C)
cor trk_* if inlist(grade, 6, 7, 8) [weight=schsz]
matrix cor_out_68 = r(C)

matrix out_0 = cor_out_48 \ J(1, colsof(cor_out_48), .) \ cor_out_45 \ J(1, colsof(cor_out_48), .) \ cor_out_68
matrix out_1 = J(rowsof(cor_out_48), 1, 48) \ . \ J(rowsof(cor_out_45), 1, 45) \ . \ J(rowsof(cor_out_68), 1, 68)
matrix cor_out = out_0 , out_1
mat2txt, matrix(cor_out) saving("$OUTFILES/tracking_9-t2.txt") replace

/************************************************************
Figure 3: Peer dispersion
F3-clssd
T-clssd
stats cited
*************************************************************/

use "$WORKING\tracking_7.dta", clear

keep if (subj == "math")
keep campus grade year schsz zt* CGa*

gen trk_sch_fa = ztrackR
gen trk_sch_mr = (ztrackR - ztrackRavg) / (ztrackRavg_max - ztrackRavg)

gen sd_wt = schsz * CGavgclssd

gen is_elem = (grade <= 5)
gen is_mid = (grade >= 6)
gen is_all = 1

save "$WORKING\temp/tracking_9_aq_0.dta", replace

foreach ss in fa mr {
    foreach em in elem mid all {
	    use "$WORKING\temp/tracking_9_aq_0.dta", clear
		
		keep if is_`em'
		
		gen t0 = floor(trk_sch_`ss' * 20) / 20
		collapse (sum) schsz sd_wt, by(t0)
		replace schsz = . if schsz <= 5
		
		gen cls_sd_`ss'_`em' = sd_wt / schsz
		rename schsz schsz_`ss'_`em'
		keep t0 cls_sd_`ss'_`em' schsz_`ss'_`em'
		
		save "$WORKING\temp/tracking_9_aq_`ss'_`em'.dta", replace
	}
}

use "$WORKING\temp/tracking_9_aq_fa_elem.dta", clear
merge 1:1 t0 using "$WORKING\temp/tracking_9_aq_mr_elem.dta"
drop _merge
merge 1:1 t0 using "$WORKING\temp/tracking_9_aq_fa_mid.dta"
drop _merge
merge 1:1 t0 using "$WORKING\temp/tracking_9_aq_mr_mid.dta"
drop _merge
merge 1:1 t0 using "$WORKING\temp/tracking_9_aq_fa_all.dta"
drop _merge
merge 1:1 t0 using "$WORKING\temp/tracking_9_aq_mr_all.dta"
drop _merge

sort t0

keep if (t0 <= 1.5) & (t0 >= -0.5)

export delimited using "$OUTPUT\tracking_9-f3.csv", replace


/************************************************************
distribution of campus types
*************************************************************/

use "$WORKING/tracking_6-math.dta", clear

keep campus grade year C*
duplicates drop campus grade year, force

save "$WORKING/temp/tracking_9-camp0.dta", replace

// la var Ct_reg "instructional campus"
// la var Ct_altrg "alternative instr unit, regular acct system"
// la var Ct_altsp "alternative instr unit, alt acct system"
// la var Ct_juv "juvenile justice alt ed pgm"
// la var Ct_daep "DAEP only campus"
// la var Ct_chrtd "member of open enr chrtr dist w/<75% at risk stud"
// la var Ct_chrtc "campus-level charter"
// la var Ct_chrtu "member of open enr college chrtr dist"

use "$WORKING\temp\tracking_9-camp0.dta", clear

drop Ct_chrt*

egen ncamp = tag(year campus)
collapse (sum) ncamp CGenrl, by(year Ct_*)

export delimited using "$OUTPUT\tracking_9-camp1.csv", replace

use "$WORKING\temp\tracking_9-camp0.dta", clear

egen ncamp = tag(year campus)
collapse (sum) ncamp CGenrl, by(year Ct_chrt*)

export delimited using "$OUTPUT\tracking_9-camp2.csv", replace


/************************************************************
Figures C4 and C5: Tracking in Each Subject By Math Score
FC4-subj, FC5-subj
*************************************************************/

import delimited "$WORKING/tracking_7.csv", clear

gen trk_fa = ztrackr
gen trk_mr = (ztrackr - ztrackravg) / (ztrackravg_max - ztrackravg)
keep subj campus grade year trk_fa trk_mr schsz

gen trk_fa0 = floor(trk_fa * 20) / 20
gen trk_mr0 = floor(trk_mr * 20) / 20
gen one0 = 1

save "$WORKING/temp/tracking_9-fc4_0.dta", replace

use "$WORKING/temp/tracking_9-fc4_0.dta", clear

collapse (sum) schsz, by(subj grade trk_mr0)
reshape wide schsz, i(subj trk_mr0) j(grade)
reshape long schsz, i(subj trk_mr0) j(grade)
replace schsz = . if (schsz <= 5)
egen tmp1 = sum(schsz), by(subj grade)
gen pct = schsz / tmp1
drop tmp*
reshape wide schsz pct, i(subj trk_mr0) j(grade)

rename trk_mr0 trk0
gen fa_mr = "mr"

save "$WORKING/temp/tracking_9-fc4_1.dta", replace

use "$WORKING/temp/tracking_9-fc4_0.dta", clear

collapse (sum) schsz, by(subj grade trk_fa0)
reshape wide schsz, i(subj trk_fa0) j(grade)
reshape long schsz, i(subj trk_fa0) j(grade)
replace schsz = . if (schsz <= 5)
egen tmp1 = sum(schsz), by(subj grade)
gen pct = schsz / tmp1
drop tmp*
reshape wide schsz pct, i(subj trk_fa0) j(grade)

rename trk_fa0 trk0
gen fa_mr = "fa"

save "$WORKING/temp/tracking_9-fc4_2.dta", replace
append using "$WORKING/temp/tracking_9-fc4_1.dta"

order fa_mr subj trk* schsz* pct*


export delimited "$OUTPUT/tracking_9-fc4.csv", replace


log close
